<?php

include("CloudDBInterface.php");

$instance = new CloudDBInterface();

function getSchedule() {
    
    global $instance;

    $query =    "SELECT Schedule.Id, startTime, endTime, date, CONCAT(Fname,' ', Lname), Tips
                FROM Schedule, Employee
                WHERE Schedule.EmployeeId = Employee.id";

    $result = $instance -> selectQuery($query);

    return $result;
}

function getEmployee($id){

	global $instance;

	$query = "SELECT Employee.id,Fname,Lname,HireDate,PhoneNumber,Address,HourlyRate,name FROM Employee,Employee_Type WHERE Employee.id = ".$id." AND Employee_Type.id=EmployeeTypeId";
	$result =  $instance -> selectQuery($query);
	
	return $result;
}

function getEmployeeSchedule($id){

	global $instance;

	$query = "SELECT date, startTime, endTime, tips 
				FROM Employee, Schedule 
				WHERE Employee.id = Schedule.EmployeeId
				AND EmployeeId = $id";
	$result = $instance -> selectQuery($query);

	return $result;
}

function getEmployees(){

	global $instance;

	$query = "SELECT Employee.id,Fname,Lname,HireDate,PhoneNumber,Address,HourlyRate,name FROM Employee,Employee_Type WHERE Employee_Type.id=EmployeeTypeId";
	
	$result =  $instance -> selectQuery($query);
	
	return $result;
}

function getEmployeesSorted($sort, $startDate = null, $endDate = null, $id = null){

	global $instance;
	
	$query = "SELECT Employee.id,Fname,Lname,HireDate,PhoneNumber,Address,HourlyRate,name FROM Employee,Employee_Type WHERE Employee_Type.id=EmployeeTypeId";
	if($startDate != null && $endDate != null) {
		$query = $query . ' AND HireDate BETWEEN \''.$startDate.'\' AND \''.$endDate.'\'';
	}
	if($id != null){
		$query = $query . ' AND Employee.id='.$id;
	}
	
	if($sort == 'Seniority') {
		$query = $query . ' ORDER BY Employee.HireDate DESC';
	}
	else if($sort=='Salary') {
		$query = $query . ' ORDER BY Employee.HourlyRate DESC';
	}

	$result =  $instance -> selectQuery($query);
	
	return $result;
}

function getScheduleSorted($sort, $employee = null, $startDate=null, $endDate=null){
	
	global $instance;

        $query =    "SELECT Schedule.Id, startTime, endTime, date, CONCAT(Fname,' ', Lname), Tips
                    FROM Schedule, Employee
                    WHERE Schedule.EmployeeId = Employee.id";
        
	
	if($startDate != null && $endDate != null) {
		$query = $query . ' AND date BETWEEN \''.$startDate.'\' AND \''.$endDate.'\'';
	}
	if($employee != "" && !is_null($employee) && !is_NaN($employee) ) {
		$query = $query . ' AND Employee.id ='.$employee;
	}
	
	if($sort == 'Date') {
		$query = $query . ' ORDER BY date DESC';
	}
	else if($sort =='Employee') {
		$query = $query . ' ORDER BY CONCAT(Fname,\' \', Lname) ASC';
	}
	
	$result = $instance -> selectQuery($query);

	return $result;
}

function getTransactionsSorted($sort, $employee = null, $startDate=null, $endDate=null){
	
	global $instance;

	$query = "SELECT Transactions.Id, Model, Color, Year, Price, Commission, Total, CONCAT(Fname,' ', Lname), Date FROM Transactions, Employee, Inventory WHERE InventoryId=Inventory.Id AND EmployeeId=Employee.id";

	
	if($startDate != null && $endDate != null) {
		$query = $query . ' AND Date BETWEEN \''.$startDate.'\' AND \''.$endDate.'\'';
	}
	if($employee != "" && !is_null($employee) && !is_NaN($employee) ) {
		$query = $query . ' AND Employee.id ='.$employee;
	}
	
	if($sort == 'Price') {
		$query = $query . ' ORDER BY Price DESC';
	}
	else if($sort=='Date') {
		$query = $query . ' ORDER BY Date DESC';
	}
	
	$result = $instance -> selectQuery($query);

	return $result;
}

function getWashTransactionsSorted($car,$startDate=null, $endDate=null){
	
	global $instance;

	$query = "Select Wash_History.id, Car_Type.Name, extraId, Extras.Cost, date FROM Wash_History, Car_Type, Wash_Extras, Extras WHERE Wash_History.id = Wash_Extras.washId AND carTypeId=Car_Type.id AND extraId=Extras.id";

	
	if($startDate != null && $endDate != null) {
		$query = $query . ' AND Date BETWEEN \''.$startDate.'\' AND \''.$endDate.'\'';
	}
	if($car != 'ALL') {
		$query = $query . ' AND Car_Type.Name =\''.$car.'\'';
	}
	$query = $query . ' ORDER BY washId,extraId';
	$result = $instance -> selectQuery($query);

	return $result;
}

function getTransactions(){
	
	global $instance;

	$query = "SELECT Transactions.Id, Model, Color, Year, Price, Commission, Total, CONCAT(Fname,' ', Lname), Date FROM Transactions, Employee, Inventory WHERE InventoryId=Inventory.Id AND EmployeeId=Employee.id";

	$result = $instance -> selectQuery($query);

	return $result;
}

function getWashTransactions(){

	global $instance;

	$query = "Select Wash_History.id, Car_Type.Name, extraId, Extras.Cost, date 
				FROM Wash_History, Car_Type, Wash_Extras, Extras 
				WHERE Wash_History.id = Wash_Extras.washId 
				AND carTypeId=Car_Type.id 
				AND extraId=Extras.id 
				ORDER BY washId,extraId";

	$result = $instance -> selectQuery($query);

	return $result;	
}

function getInventory(){
	
	global $instance;

	$query = "SELECT * FROM Inventory";

	$result = $instance -> selectQuery($query);

	return $result;
}

function getInventoryFiltered($filter){
	
	global $instance;

	$query = "SELECT * FROM Inventory WHERE Sold LIKE '%".$filter."%'";

	$result = $instance -> selectQuery($query);

	return $result;
}

function getInventorySorted($sort,$model=null,$color=null,$year=null,$filter=null){
	
	global $instance;

	$query = "SELECT * FROM Inventory ";


	if($year != null || $model != null || $color != null || $filter != null){
		$query = $query . "WHERE TRUE ";
	}
	

	if($year != null) {
		$query = $query . ' AND Year =' . $year; 
	}
	if($model != null){
		$query = $query . " AND Model LIKE '%" . $model . "%'"; 	
	}
	if($color != null){
		$query = $query . " AND Color LIKE '%" . $color . "%''"; 	
	}
	if($filter != null) {
		$query = $query . " AND Sold LIKE '%" . $filter . "%'"; 	
	}
	if($sort == 'Model'){
		$query = $query . ' ORDER BY Model';
	}
	else if($sort == 'Color'){
		$query = $query . ' ORDER BY color';
	}
	else if($sort == 'Year'){
		$query = $query . ' ORDER BY year';
	}

	$result = $instance -> selectQuery($query);

	return $result;
}

function getServices(){

	global $instance;

	$query = "SELECT * FROM Rates";

	$result = $instance -> selectQuery($query);

	return $result;	
}

function getExpenses() {
    
    global $instance;
    
    //Split into 2 queries because of CloudDB's fault!!
    
    $query =    "SELECT Expenses.id, Expenses.cost, Expenses.expenseTypeId, Expense_Type.description
                FROM Expenses, Expense_Type
                WHERE Expenses.expenseTypeId = Expense_Type.id";
    
    $result1 = $instance->selectQuery($query);
    
    $query =    "SELECT Expense_Type.id, Occurrence.description
                FROM Expense_Type, Occurrence
                WHERE Expense_Type.occurrenceId = Occurrence.id";
    
    $result2 = $instance->selectQuery($query);
    
    //output array;
    $output = array();
    
    //Combine results
    foreach ($result1 as $row) {
        
        $expenseTypeId = $row["expenseTypeId"];
        
        //Look in 2nd array
        foreach ($result2 as $row1) {
            
            //Find a match and "JOIN"
            if ($row1["id"] == $expenseTypeId) {
                
                //Add freq and add to output.
                $row["frequency"] = $row1["description"];
                $output[] = $row;
            }
        }
    }
        
    return $output;
}

//Descriptor for sorting.
function compareDescription($a, $b) {
    
    return strnatcasecmp($a["description"], $b["description"]);
}

//Sort expenses by description.
function getExpensesDescriptionSort() {
    
    $data = getExpenses();
    
    usort($data, 'compareDescription');
    
    return $data;
}

//Descriptor for sorting
function compareFrequency($a, $b) {
    
    return strnatcasecmp($a["frequency"], $b["frequency"]);
}

//Sort expeneses by frequency.
function getExpensesFrequencySort() {
    
    $data = getExpenses();
    
    usort($data, 'compareFrequency');
    
    return $data;
}

function generateTableBody($data) {
	//Each tuple
	foreach($data as $rows) {
		echo "<tr>";
			foreach($rows as $column) {
				echo "<td>".$column."</td>";
			}
		echo "</tr>";
	}
}

function generateEmployeeTableBody($data) {
	//Each tuple
	
	foreach($data as $rows) {
		echo "<tr>";
			foreach($rows as $column) {
				echo "<td><a href='specificEmployee.php?id=".$rows['id']."'>".$column."</a></td>";
			}
		echo "</tr>";
	}
}

function executeQuery($query,$type){
	global $instance;

	if($type == "select"){
		return $instance -> selectQuery($query);
	}
	else if($type =="drop"){
		return $instance -> deleteTable($query);
	}
	else if($type == "insert"){
		return $instance -> insertQuery($query);
	}
	else if($type == "update"){
		return $instance -> updateQuery($query);
	}
	else if($type == "all"){
		return $instance -> listTables();	
	}
	else{
		die("Unkown Command");
	}
}

/**
 * Gets all employee types and prints them as a drop down menu. 
 */
function employeeTypes() {
    
    global $instance;
    
    //Get the json result from query.
    $res = $instance->selectQuery("SELECT name FROM Employee_Type");
    
    print "<select name=\"employeeType\" id=\"employeeType\">";    
    
    //Print each type to form.
    foreach ($res as $rows) {
        
        foreach ($rows as $column) {
            
            print "<option>$column</option>";
        }
    }
    
    print "</select>";
}

/**
 *Gets all available cars and prints them as a drop down menu. 
 */
function availableCars() {
    
    global $instance;
    
    //Get the json result from query.
    $res = $instance->selectQuery("SELECT * FROM Inventory WHERE Sold <> \"Sold\"");
    
    print "<select name=\"carSold\" id=\"carSold\">";    
    
    //Print each type to form.
    foreach ($res as $rows) {
        
        //Get info.
        $car = $rows["Id"].": ".$rows["Model"]." ".$rows["Year"]." ".$rows["Color"]." - $".$rows["Price"];
         
        //Print it to the page.
        print "<option>$car</option>";
    }
    
    print "</select>";
}

/**
 *Gets all employee names. 
 */
function employeeNames() {
    
    global $instance;
    
    //Get the json result from query.
    $res = $instance->selectQuery("SELECT id, FName, LName FROM Employee");
    
    print "<select name=\"employee\" id=\"employee\">";    
    
    //Print each type to form.
    foreach ($res as $rows) {
        
        //Get info.
        $employee = $rows["id"].": ".$rows["Fname"]." ".$rows["Lname"];
         
        //Print it to the page.
        print "<option>$employee</option>";
    }
    
    print "</select>";
}

/**
 *Gets all car types. 
 */
function carTypes() {
    
    global $instance;
    
    //Get the json result from query.
    $res = $instance->selectQuery("SELECT * FROM Car_Type");
    
    print "<select name=\"carType\" id=\"carType\">";    
    
    //Print each type to form.
    foreach ($res as $rows) {
        
        //Get info.
        $carType = $rows["Id"].": ".$rows["Name"];
         
        //Print it to the page.
        print "<option>$carType</option>";
    }
    
    print "</select>";
}

/**
 *Gets all extras. 
 */
function extras() {
    
    global $instance;
    
    //Get the json result from query.
    $res = $instance->selectQuery("SELECT * FROM Extras");
    $name = "extrasHidden";
    $count = 0;
    
    //Print N menus.
    foreach ($res as $lines) {
      
        //Print hidden for JS reference. Increment the count for names
        print "<select style=\"display: none\" name=\"$name$count\" id=\"$name$count\">";

        //Print each type to form.
        foreach ($res as $rows) {

            //Get info.
            $extra = $rows["Id"].": ".$rows["Name"]." ".$rows["Cost"];

            //Print it to the page.
            print "<option>$extra</option>";
        }

        print "</select>";
        print "<input type=\"hidden\" name=\"On$name$count\" id=\"On$name$count\" value=\"false\"/>";
        
        //Increment counter.
        $count++;
    }   
}

/**
 *Gets all expense types and prints as a form. 
 */
function expenseTypes() {
    
    global $instance;
    
    //Get the json result from query.
    $res = $instance->selectQuery("SELECT * FROM Expense_Type");
    
    print "<select name=\"expenseType\" id=\"expenseType\">";    
    
    //Print each type to form.
    foreach ($res as $rows) {
        
        //Get info.
        $et = $rows["id"].": ".$rows["description"];
         
        //Print it to the page.
        print "<option>$et</option>";
    }
    
    print "</select>";
}

/**
 *Gets all expense types and prints as a form. 
 */
function occurrences() {
    
    global $instance;
    
    //Get the json result from query.
    $res = $instance->selectQuery("SELECT * FROM Occurrence");
    
    print "<select name=\"occurrence\" id=\"occurrence\">";    
    
    //Print each type to form.
    foreach ($res as $rows) {
        
        //Get info.
        $occ = $rows["id"].": ".$rows["description"];
         
        //Print it to the page.
        print "<option>$occ</option>";
    }
    
    print "</select>";
}

?>